from utils.sql_utils import SQLManager
from utils.time_utils import TimeUtils
from utils.id_utils import IdUtils
from flask import request, jsonify

class CommentSelectService:
    def select_comments_by_note_id(self, note_id,current):
        """
        根据 note_id 查询所有评论
        """
        sql = """
            SELECT 
                rnc.unique_id AS comment_id,
                rnc.content AS comment_content,
                rnc.like_count AS comment_like_count,
                rnc.created_at AS comment_created_at,

                rau.unique_id AS user_id,
                rau.admin_user_name AS user_name,
                rau.avatar_url AS user_avatar_url
            FROM 
                rb_note_comments rnc,
                rb_admin_user rau
            WHERE 
                rau.unique_id = rnc.user_id
            AND
                note_id = %s
            ORDER BY 
                rnc.created_at DESC;
        """
        values = (note_id,)
        return SQLManager().get_list_by_page(sql, values,int(current))

    def select_sub_comments_by_comment_id(self, comment_id,current):
        """
        根据 comment_id 查询所有子评论
        """
        sql = """
            SELECT 
                rnsc.unique_id AS sub_comment_id,
                rnsc.content AS sub_comment_content,
                rnsc.comment_id AS sub_comment_content_belong,
                rnsc.like_count AS sub_comment_like_count,
                rnsc.created_at AS sub_comment_created_at,

                rau.unique_id AS user_id,
                rau.admin_user_name AS user_name,
                rau.avatar_url AS user_avatar_url
            FROM 
                rb_note_sub_comments rnsc,
                rb_admin_user rau
            WHERE 
                rau.unique_id = rnsc.user_id
            AND
                comment_id = %s;
        """

        values = (comment_id,)
        return SQLManager().get_list_by_page(sql, values,int(current))

    def select_comment_by_id(self, comment_id):
        """
        根据 comment_id 查询单个评论
        """
        sql = """
            SELECT 
                unique_id, note_id, user_id, content, created_at
            FROM 
                rb_note_comments
            WHERE 
                unique_id = %s;
        """
        values = (comment_id,)
        return SQLManager().get_one(sql, values)

    def select_sub_comment_by_id(self, sub_comment_id):
        """
        根据 sub_comment_id 查询单个子评论
        """
        sql = """
            SELECT 
                unique_id, comment_id, user_id, content, created_at
            FROM 
                rb_note_sub_comments
            WHERE 
                unique_id = %s;
                
        """
        values = (sub_comment_id,)
        return SQLManager().get_one(sql, values)

    def select_comments_with_sub_comments(self, note_id):
        """
        根据 note_id 查询所有评论及其子评论
        """
        comments = self.select_comments_by_note_id(note_id)
        for comment in comments:
            comment['sub_comments'] = self.select_sub_comments_by_comment_id(comment['unique_id'])
        return comments

    def select_comments_by_user_id(self, user_id):
        """
        根据 user_id 查询用户的所有评论
        """
        sql = """
            SELECT 
                unique_id, note_id, user_id, content, created_at
            FROM 
                rb_note_comments
            WHERE 
                user_id = %s
            ORDER BY 
                created_at DESC;
        """
        values = (user_id,)
        return SQLManager().get_list(sql, values)

    def select_sub_comments_by_user_id(self, user_id):
        """
        根据 user_id 查询用户的所有子评论
        """
        sql = """
            SELECT 
                unique_id, comment_id, user_id, content, created_at
            FROM 
                rb_note_sub_comments
            WHERE 
                user_id = %s
            ORDER BY 
                created_at DESC;
        """
        values = (user_id,)
        return SQLManager().get_list(sql, values)
    
    def select_count_collection(request):
        user_id = request.get_json().get('user_id')
        note_id = request.get_json().get('note_id')
        sql = """
            SELECT 
                COUNT(*) AS count
            FROM 
                rb_note_collections
            WHERE 
                user_id = %s
            AND
                note_id = %s;
        """
        values = (user_id, note_id)
        return SQLManager().get_one(sql, values)